package gx.dao.impl.common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.jdbc.ReturningWork;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import gx.dao.PurchaseItemDao;
import gx.dao.impl.common.BaseDaoImpl;
import gx.entity.PurchaseItem;

/**
 * @author 王诗桐
 * 采购项DAO实现层
 */

@Repository
public class PurchaseItemDaoImpl extends BaseDaoImpl<PurchaseItem> implements PurchaseItemDao{

	//查询采购订单为ID的
	public List<PurchaseItem> findByPurId(Integer id){
		return find("from PurchaseItem where purc_id = ? ",id);
	}

	//指定年中按月统计商品入库信息
	@Override
	public Map<String, Long> totaInventory(final int year) {
		
		//入库 status = 2
		final String sql = "select month(p.pur_date) m,sum(pi.num) "
				        + "n from t_purchase_item pi join "
				        + "t_purchase p on pi.purc_id = p.id "
				        + "where p.status = 2 and year(p.pur_date)= ? "
				        + "group by month(p.pur_date)";
		Map<String, Long> map = getSession().doReturningWork(
				new ReturningWork<Map<String, Long>>() {

					@Override
					public Map<String, Long> execute(Connection conn) throws SQLException {
						Map<String, Long> mps = new LinkedHashMap<String,Long>();
						PreparedStatement ps =(PreparedStatement) conn.prepareStatement(sql);
						ps.setInt(1, year);
						ResultSet rs = ps.executeQuery();
						while(rs.next()){
							String month = rs.getString(1);
							Long num = rs.getLong(2);
							mps.put(month, num);
						} 
						return mps;
					}
				});
		return map;
	}

	//条件：年份、入库、按分类比例
	@Override
	public Map<String, Long> totaInventoryByCategory(final int year) {
		//入库 status = 2
				final String sql = "select c.name m,sum(pi.num) "
						        + "n from t_purchase_item pi "
						        + "join t_purchase p on pi.purc_id = p.id "
						        + "join t_product pr on pi.prod_id = pr.id "
						        + "join t_category c on pr.cate_id = c.id "
						        + "where p.status = 2 and year(p.pur_date)= ? "
						        + "group by c.id";
				Map<String, Long> map = getSession().doReturningWork(
						new ReturningWork<Map<String, Long>>() {

							@Override
							public Map<String, Long> execute(Connection conn) throws SQLException {
								Map<String, Long> mps = new LinkedHashMap<String,Long>();
								PreparedStatement ps =(PreparedStatement) conn.prepareStatement(sql);
								ps.setInt(1, year);
								ResultSet rs = ps.executeQuery();
								while(rs.next()){
									String name = rs.getString(1);
									Long num = rs.getLong(2);
									mps.put(name, num);
								} 
								return mps;
							}
						});
				return map;
	}
	@Override
	public Map<String, String> Storecount2() {

//		final String sql = "select pr.name,sum(pi.num) n ,pr.security_num from t_purchase_item pi "
//				+ "join t_product pr on pi.prod_id = pr.id " + "join t_purchase p on pi.purc_id = p.id "
//				+ "where p.status = 2 " + "group by pr.id";
		final String sql="select pr.name,pr.num,pr.security_num from t_product pr group by pr.id";

		Map<String, String> map = getSession().doReturningWork(new ReturningWork<Map<String, String>>() {
			@Override
			public Map<String, String> execute(Connection conn) throws SQLException {
				Map<String, String> mps = new LinkedHashMap<String, String>();
				PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					String name = rs.getString(1);
					Long num = rs.getLong(2);
					Integer snum = rs.getInt(3);
					mps.put(name, num + "," + snum);
				}
				return mps;
			}
		});

		return map;
	}

	@Override
	public Map<String, Long> Storecount() {
		final String sql = "select pr.name,sum(pi.num) n from t_purchase_item pi "
				+ "join t_product pr on pi.prod_id = pr.id "
				+ "join t_purchase p on pi.purc_id = p.id "
				+ "where p.status = 2 "
				+ "group by pr.id";

		Map<String, Long> map = getSession().doReturningWork(
				new ReturningWork<Map<String, Long>>() {
					@Override
					public Map<String, Long> execute(Connection conn)
							throws SQLException {
						Map<String, Long> mps = new LinkedHashMap<String, Long>();
						PreparedStatement ps = (PreparedStatement) conn
								.prepareStatement(sql);
						ResultSet rs = ps.executeQuery();
						while (rs.next()) {
							String name = rs.getString(1);
							Long num = rs.getLong(2);
							mps.put(name, num);
						}
						return mps;
					}
				});

		return map;
	}
}
